package com.onlineMIS.ORM.DAO.headQ.inventory;

import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.onlineMIS.ORM.DAO.BaseDAO;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrder;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrderProduct;
import com.onlineMIS.ORM.entity.headQ.preOrder.CustPreorderIdentity;
import com.onlineMIS.action.headQ.report.HeadQReportFormBean;
import com.onlineMIS.common.Common_util;
import com.onlineMIS.common.loggerLocal;
import com.onlineMIS.filter.SystemParm;

@Repository
public class InventoryOrderProductDAOImpl extends BaseDAO<InventoryOrderProduct> {
	@SuppressWarnings("unchecked")
	public boolean deleteInventoryProducts(final Set<Integer> delete_id) {
		String parameter = "?";
		for (int i =1;i<delete_id.size();i++)
			parameter +=",?";
		final String hql = "delete from InventoryOrderProduct p where p.ID in ("+parameter+")";
		try{
			this.getHibernateTemplate().execute(new HibernateCallback(){
	            public Object doInHibernate(Session session) throws HibernateException,SQLException{
	            	Query q= session.createQuery(hql);
	            	int i = 0;
	    			for(Integer id: delete_id) {
	    				q.setParameter(i,id);
	    				i++;
	    			}
	    			int success = q.executeUpdate();
	    			return success;
	           }
	     });
		} catch (Exception e) {
			loggerLocal.error(e);
		    return false;
		}
		return true;
	}
	
	public InventoryOrderProduct getByOrderIdProductId(int orderId, int productId){
		DetachedCriteria criteria = DetachedCriteria.forClass(InventoryOrderProduct.class);
		criteria.add(Restrictions.eq("order.order_ID", orderId));
		criteria.add(Restrictions.eq("productBarcode.id", productId));
		
		List<InventoryOrderProduct> products = this.getByCritera(criteria, true); 
		if (products.size() > 0)
			return products.get(0);
		else 
			return null;
	}
	
	@SuppressWarnings("unchecked")
	public boolean deleteInventoryProducts(int orderId) {
		Object[] values = new Object[]{orderId};
		String hql = "delete from InventoryOrderProduct p where p.order.order_ID = ?";
		
		this.executeHQLUpdateDelete(hql, values, true);
		
		return true;
	}
	
	public int checkBarcodeInOrder(int productBarcodeId) {
        String hql = "select count(p.ID) from InventoryOrderProduct p where p.productBarcode.id = ?";
        int count = 0;
        try{
        	Object[] values = new Object[]{productBarcodeId};
        	count = this.executeHQLCount(hql, values, true);
		} catch (Exception e){
			loggerLocal.error(e);
		}
		
		return count;
	}

	
	/**
	 * 单位销售统计-根据货品信息查询inventoty_order_product中符合条件的product barcode id
	 * @param formBean
	 * @return
	 */
	public List<Integer> getProductIds(HeadQReportFormBean formBean) {
		String sql = "select DISTINCT( s3.productBarcode.id) from Product s1,ProductBarcode s2, InventoryOrderProduct s3, InventoryOrder s4  where s1.productId = s2.product.id  and  s2.id = s3.productBarcode.id and s4.order_ID = s3.order.order_ID  ";
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		List<Integer> orderIds = new ArrayList<Integer>();
		
		if (formBean.getBrand().getBelong_To() !=Common_util.ALL_RECORD) {
			sql += "  and  s1.brand.belong_To = ?";
			list.add(formBean.getBrand().getBelong_To());
		}
		if (formBean.getYear().getYear_ID() != 0) {
			sql += "  and s1.year.year_ID = ?";
			list.add(formBean.getYear().getYear_ID());
		}
		if (formBean.getQuarter().getQuarter_ID() != 0) {
			sql += "  and s1.quarter.quarter_ID = ?";
			list.add(formBean.getQuarter().getQuarter_ID());
		}
		if (formBean.getBrand().getBrand_ID() != 0) {
			sql += "  and s1.brand.brand_ID = ?";
			list.add(formBean.getBrand().getBrand_ID());
		}
		if (!StringUtils.isEmpty(formBean.getBarcode())) {
			sql += "  and s2.barcode = ?";
			list.add(formBean.getBarcode());
		}
		sql += " and s4.order_StartTime between ? and ? ";
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));
		
		List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);
		if (values.size() != 0) {
			for (Object object : values) {
					orderIds.add(Common_util.getInt(object));
			}
		}


		return orderIds;
	}

	
	/**
	 * 根据货品条件已经筛选的productIds进行时间范围内的二次筛选
	 * @param formBean
	 * @param productIds
	 * @return
	 */
	public List<Integer> selectProductIds(HeadQReportFormBean formBean) {
		String sql = "select DISTINCT( s2.productBarcode.id) from InventoryOrder s1, InventoryOrderProduct s2 where s1.order_ID = s2.order.order_ID  and s1.order_StartTime between ? and ? ";
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		List<Integer> orderIds = new ArrayList<Integer>();
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));

		try {
			List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);
			if (values.size() != 0) {
				for (Object object : values) {
					orderIds.add(Common_util.getInt(object));
				}
			}
		} catch (Exception e) {
			loggerLocal.error(e);
		}
		return orderIds;
	}

	/**
	 * 根据单据ID查询单据配货数量
	 * @param order_ID
	 * @return
	 */
	public long selectCompleteNums(int order_ID) {
        String hql = "select SUM(quantityPeihuo) from InventoryOrderProduct  where order.order_ID = "+order_ID;
		long nums = 0;
		try {
		List<Object> values = this.executeHQLSelect(hql.toString(), null, null, false);
		if (values!= null && values.size() != 0) {
			for (Object record : values) {	
				nums = (long) record;
			}
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return nums;
	}
	
	/**
	 * 根据单据ID查询单据配货数量
	 * @param order_ID
	 * @return
	 */
	public Map<Integer, Integer> selectCompleteNums(String orderIdSet) {
		Map<Integer, Integer> orderCompleteMap = new HashMap<Integer, Integer>();
		
	      String hql = "select SUM(quantityPeihuo), order.order_ID from InventoryOrderProduct  where order.order_ID in "+orderIdSet +" GROUP BY order.order_ID";
			int nums = 0;
			int orderId = 0;

			List<Object> values = this.executeHQLSelect(hql.toString(), null, null, false);
			if (values!= null && values.size() != 0) {
				for (Object record : values) {	
					Object[] records = (Object[])record;
					nums = Common_util.getInt(records[0]);
					orderId = Common_util.getInt(records[1]);
					
					orderCompleteMap.put(orderId, nums);
				}
			}

			return orderCompleteMap;
	}

	/**
	 * 根据单件ID查询单件货品集合
	 * @param orderId
	 * @return
	 */
	public List<InventoryOrderProduct> selectProductsByOrderId(int orderId) {
		DetachedCriteria criteria = DetachedCriteria.forClass(InventoryOrderProduct.class);
		criteria.add(Restrictions.eq("order.order_ID", orderId));
		
		return this.getByCritera(criteria, true); 
	}


	/**
	 * 查询已配数量
	 * @return
	 * @throws ParseException 
	 */
	public int selectIpadNoEquidppedNums(int pbId, int status)  {
		String sql = "select sum(s2.quantityPeihuo) from InventoryOrder s1,InventoryOrderProduct s2 where s1.order_ID = s2.order.order_ID and  s1.order_Status= ? and s2.productBarcode.id = ? and s1.client_id != "+SystemParm.getQxCustStocktaking();
		List<Object> list = new ArrayList<Object>();
		list.add(status);
		list.add(pbId);
		
		int nums = 0;
		try {
		List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, false);
		if (values!= null && values.size() != 0) {
			for (Object record : values) {	
				if (record != null) {
					nums += (long)record;
				}	
			}
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return nums;
		
	}
	
	/**
	 * 查询选货数量
	 * @return
	 * @throws ParseException 
	 */
	public int selectQuantiy(int pbId)  {
		String sql = "select sum(s2.quantity) from InventoryOrder s1,InventoryOrderProduct s2 where s1.order_ID = s2.order.order_ID and (  s1.order_Status= ? or s1.order_Status= ? or s1.order_Status= ? ) and s2.productBarcode.id = ? and s1.client_id != "+SystemParm.getQxCustStocktaking();
		List<Object> list = new ArrayList<Object>();
		list.add(InventoryOrder.STATUS_DRAFT);
		list.add(InventoryOrder.STATUS_WAITING_AUDIT);
		list.add(InventoryOrder.STATUS_ACCOUNT_PROCESS);
		list.add(pbId);
		
		int nums = 0;
		try {
		List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, false);
		if (values!= null && values.size() != 0) {
			for (Object record : values) {	
				if (record != null) {
					nums += (long)record;
				}	
			}
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return nums;
		
	}
	
	
	/**
	 * 查询已配数量
	 * @return
	 * @throws ParseException 
	 */
	public int selectIpadEquidppedNums(List<Integer> custIds)  {
		String clientIdSql = Common_util.parseList2SQLString(custIds);
		
		String sql = "select sum(s2.quantityPeihuo) from InventoryOrder s1,InventoryOrderProduct s2 where s1.order_ID = s2.order.order_ID and  s1.order_StartTime between ? and ?  and s1.client_id in "+clientIdSql+" and s1.order_Status="+ InventoryOrder.STATUS_PEIHUO_COMPLETE;
		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(Common_util.getToday()));
		list.add(Common_util.formEndDate(Common_util.getToday()));
		
		int nums = 0;
		try {
		List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, false);
		if (values!= null && values.size() != 0) {
			for (Object record : values) {	
				if (record != null) {
					nums += (long)record;
				}	
			}
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return nums;
		
	}



}
